Shareware Super Platinum 8/Shareware Super Platinum 8.iso/mac/DATABASE/TECH91.ZIP;1/TI784.ASC

Text File | 1991-08-26 | 5.3 KB | 199 lines

  PRODUCT  :  Paradox                                NUMBER  :  784
  VERSION  :  3.0 & up
       OS  :  DOS
     DATE  :  August 26, 1991                          PAGE  :  1/3

    TITLE  :  Doing "Not-In" Queries in Paradox




  This query is made to ask the question "What records exist in one
  table that do NOT exist in another?"  The following is a general
  example method for creating "Not-In" queries.

  Notice that this example uses the COUNT operation and it does not
  use NOT or NO.  For the examples, an underscore character will be
  used in place of the [F5] key to indicate an example element in
  the query.

       First create tables with the structures shown below.

  CUSTOMERÀÕÕÕÕCust #ÕÕÕÕÕÀÕÕÕÕLast NameÕÕÕÕÕÀÕÕÕÕFirst NameÕÕÕÕª
       1  ∫      1015     ∫  Elkins          ∫  Eugene          ∫
       2  ∫      1020     ∫  Fenton          ∫  Mark            ∫
       3  ∫      1055     ∫  Harding         ∫  Jim             ∫
       4  ∫      1056     ∫  Wilden          ∫  Marc            ∫
       5  ∫      1319     ∫  Fenton          ∫  Teresa          ∫
       6  ∫      1341     ∫  Goves           ∫  Cynthia         ∫
       7  ∫      1342     ∫  Goves           ∫  Joseph          ∫
       8  ∫      1685     ∫  Moon            ∫  Yvonne          ∫
       9  ∫      1988     ∫  Martinez        ∫  Susan           ∫
      10  ∫      2022     ∫  Staebell        ∫  Alice           ∫


  ORDERSÕÀÕÕÕÕOrder #ÕÕÕÕÀÕÕÕÕÕDateÕÕÕÕÕÀÕÕÕÕCust #ÕÕÕÕÕÀPart #À
       1 ∫         1     ∫   7/09/89    ∫      1015     ∫  DR  ∫
       2 ∫         2     ∫   7/09/89    ∫      1341     ∫  WR  ∫
       3 ∫         3     ∫   7/09/89    ∫      1685     ∫  HA  ∫
       4 ∫         4     ∫   7/28/89    ∫      1988     ∫  HA  ∫
       5 ∫         5     ∫   8/01/89    ∫      2022     ∫  WR  ∫
       6 ∫         6     ∫   8/03/89    ∫      1988     ∫  GH  ∫
       7 ∫         7     ∫   8/04/89    ∫      1341     ∫  DR  ∫
       8 ∫         8     ∫   8/14/89    ∫      1055     ∫  VI  ∫
       9 ∫         9     ∫   8/15/89    ∫      1020     ∫  DR  ∫
      10 ∫        10     ∫   8/16/89    ∫      1341     ∫  GH  ∫
      11 ∫        11     ∫   8/18/89    ∫      1015     ∫  TB  ∫
      12 ∫        12     ∫   8/21/89    ∫      1685     ∫  TB  ∫

  (The next table is a continuation of the above orders table)

  ORDERSÕÀÕÕÕÕÕÕPriceÕÕÕÕÕÕÕÀ
       1 ∫          46.20   ∫














  PRODUCT  :  Paradox                                NUMBER  :  784
  VERSION  :  3.0 & up
       OS  :  DOS
     DATE  :  August 26, 1991                          PAGE  :  2/3

    TITLE  :  Doing "Not-In" Queries in Paradox




       2 ∫           3.96   ∫
       3 ∫          12.04   ∫
       4 ∫          12.04   ∫
       5 ∫           3.96   ∫
       6 ∫          14.52   ∫
       7 ∫          46.20   ∫
       8 ∫          65.93   ∫
       9 ∫          46.20   ∫
      10 ∫          14.52   ∫
      11 ∫          33.66   ∫
      12 ∫          33.66   ∫

  The query shown below is how to ask the question "Which customers
  have never placed an order?".  This query works for the following
  reasons.  First, it uses the 'inclusion operator' which is the
  exclamation point <!> in the [Cust #] field of the CUSTOMER
  table.  This tells Paradox to consider every customer number,
  whether or not it is found in the ORDERS table.

  Next, the ORDERS table contains the expression "count=0" in the
  [Cust #] field.  This tells Paradox to count the number of
  occurrences of each customer number in the ORDERS tables, and
  only include those which occur zero times.  In other words, show
  the customers who have not placed any orders, or show everything
  in the CUSTOMER table that is NOT in the ORDERS table.

  ORDERSÕÀOrder #ÕÀÕÕDateÕÕÕÀÕÕÕÕCust #ÕÕÕÕÀÕPart #ÕÕÀÕÕPriceÕÕÕª
         ∫        ∫         ∫  _c,count=0  ∫         ∫          ∫
         ∫        ∫         ∫              ∫         ∫          ∫
         ∫        ∫         ∫              ∫         ∫          ∫


  CUSTOMERÀÕCust #ÕÀÕLast NameÕÕÀÕFirst NameÕª
          ∫˚ _c!   ∫            ∫            ∫
          ∫        ∫            ∫            ∫
          ∫        ∫            ∫            ∫


  ANSWERÕÀÕÕCust #ÕÕÕª
       1 ∫     1056  ∫
       2 ∫     1319  ∫
       3 ∫     1342  ∫














  PRODUCT  :  Paradox                                NUMBER  :  784
  VERSION  :  3.0 & up
       OS  :  DOS
     DATE  :  August 26, 1991                          PAGE  :  3/3

    TITLE  :  Doing "Not-In" Queries in Paradox




  To help understand how this query works, try changing the
  "count=0" to a "calc count all".  The ANSWER that will be
  returned will have 10 records, since there are 10 records in the
  CUSTOMER table.  The number shown will be the number of times
  each customer appears in the ORDERS table.  And you will see that
  the records 1056, 1319 and 1342 show a count of zero, which are
  the answer to the above query.

  The information in this document came from Brian J. Smith's
  article "How To Ask 'Not-In' Queries" which first appeared in the
  October 1989 issue of "Instant Scripts", a publication of the
  LAPALS user group in Southern California.